﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web.UI;
using System.IO;
using System.Web;

namespace System
{
    public class ExcelHelper
    {
        string excelBody = "";

        public ExcelHelper()
        {
        }

        //构建XML格式的Excel
        private string BuildExcelXML()
        {
            return BuildExcelHeader() + BuildExcelBody() + BuildExcelFooder();
        }

        //判断目录是否存在，并获得绝对路径的目录
        public string GetAbsoluteFolder()
        {
            string AbsoluteFolder = HttpContext.Current.Server.MapPath("../Files/");
            //检查该目录是否存在，如果不存在，则建立
            if (!Directory.Exists(AbsoluteFolder))
            {
                Directory.CreateDirectory(AbsoluteFolder);
            }
            return AbsoluteFolder;
        }

        private void CreateUnicodeFile(string absolutePath, string p_ExcelXML, Page p_Page)
        {
            StreamWriter sw = new StreamWriter(absolutePath, false, Encoding.Unicode);
            String strExcelXML = p_ExcelXML;
            byte[] buffer = UnicodeEncoding.Default.GetBytes(strExcelXML);
            sw.WriteLine(strExcelXML);
            sw.Flush();
            sw.Close();
        }

        //页面上导出Excel
        public void OutXMLExcelFile(string absolutePath, string filename, Page p_Page)
        {
            //创建UniCode文档
            CreateUnicodeFile(absolutePath, BuildExcelXML(), p_Page);
            //读取文档
            FileStream MyFileStream = new FileStream(absolutePath, FileMode.Open);
            int FileSize = (int)MyFileStream.Length;
            byte[] buffer = new byte[FileSize];
            MyFileStream.Read(buffer, 0, FileSize);
            MyFileStream.Flush();
            MyFileStream.Close();
            //删除文档
            DeleteTmpFile(absolutePath);
            //导出文档
            p_Page.Response.Clear();
            p_Page.Response.Buffer = true;
            p_Page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
            p_Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-7");
            p_Page.Response.ContentType = "application/ms-excel";
            p_Page.Response.BinaryWrite(buffer);
            p_Page.Response.Flush();
            p_Page.Response.End();
        }


        private void DeleteTmpFile(string absolutePath)
        {
            FileInfo fi = new FileInfo(absolutePath);
            if (fi.Exists)
            {
                fi.Delete();
            }
        }

        //构建Excel头部
        private string BuildExcelHeader()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<?xml version='1.0' ?>");
            sb.Append("<?mso-application progid='Excel.Sheet'?>");
            sb.Append("<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' ");
            sb.Append("xmlns:o='urn:schemas-microsoft-com:office:office' ");
            sb.Append("xmlns:x='urn:schemas-microsoft-com:office:excel' ");
            sb.Append("xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' ");
            sb.Append("xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' ");
            sb.Append("xmlns:html='http://www.w3.org/TR/REC-html40'>");
            sb.Append(BuildExcelStyle());
            return sb.ToString();
        }
        //构建Excel主体
        private string BuildExcelBody()
        {
            return excelBody;
        }
        //构建Excel尾部
        private string BuildExcelFooder()
        {
            return "</Workbook>";
        }

        //构建Excel样式表
        private string BuildExcelStyle()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<Styles>");
            sb.Append("<Style ss:ID='Default' ss:Name='Normal'>");
            sb.Append("<Alignment ss:Vertical='Bottom'  ss:WrapText='1'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sColumn' >");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sHeader' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Center'   ss:WrapText='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            //sHeaderLeft标题居中
            sb.Append("<Style ss:ID='sHeaderLeft' >");
            sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Center'   ss:WrapText='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sContent' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='0' />"); //不折行顯示 ss:WrapText='1' 折行 
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sContentWrap' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1' />"); //不折行顯示 ss:WrapText='1' 折行 
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("</Style>");

            //sContentRight 为右对齐的数据样式表，用于数字或金额栏的呈现
            sb.Append("<Style ss:ID='sContentRight' >");
            sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='0' />");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("<NumberFormat ss:Format='#,##0'/>");
            sb.Append("</Style>");

            //sContentRightWrap 为右对齐的数据样式表，用于数字或金额栏的呈现
            sb.Append("<Style ss:ID='sContentRightWrap' >");
            sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='1' />");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("<NumberFormat ss:Format='#,##0'/>");
            sb.Append("</Style>");

            //sContentVCenter 为右对齐且垂直居中对齐额数据样式表，用于数字或金额栏的呈现
            sb.Append("<Style ss:ID='sContentVCenter' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Center'   ss:WrapText='1' />"); //不折行顯示 ss:WrapText='1' 折行 
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("</Style>");

            //sContentRightVCenter 为右对齐且垂直居中对齐额数据样式表，用于数字或金额栏的呈现
            sb.Append("<Style ss:ID='sContentRightVCenter' >");
            sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Center'   ss:WrapText='1' />");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("<NumberFormat ss:Format='#,##0'/>");
            sb.Append("</Style>");

            //sContentLeftVCenter 为左对齐且垂直居中对齐额数据样式表
            sb.Append("<Style ss:ID='sContentLeftVCenter' >");
            sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Center'   ss:WrapText='1' />");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("<NumberFormat ss:Format='#,##0'/>");
            sb.Append("</Style>");


            sb.Append("<Style ss:ID='sCell' >");
            sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='0'/>"); //不折行顯示 ss:WrapText='1' 折行 
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Bold='1'/>");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sTitle' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='0'/>"); //不折行顯示 ss:WrapText='1' 折行 
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sAuthor' >");
            sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='12' />");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sshowdate' >");
            sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='12' />");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sOrgA' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sOrgB' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#C0FFC0' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sOrgC' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#FFC080' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sSummary' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Bold='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sNotice' >");
            sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Bottom'/>");
            sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='9' />");
            sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sHeader1' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#C0FFC0' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sHeader2' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'  ss:WrapText='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#FFC080' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("<Style ss:ID='sContent1' >");
            sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1'/>");
            sb.Append("<Borders>");
            sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
            sb.Append("</Borders>");
            sb.Append("<Interior ss:Color='#99CCFF' ss:Pattern='Solid'/>");
            sb.Append("</Style>");

            sb.Append("</Styles>");
            return sb.ToString();
        }
        public void ExporTransferStrage(DataTable dt, string[,] p_Column)
        {
            int rowCount = dt.Rows.Count;
            int colCount = p_Column.GetLength(0);
            StringBuilder sb = new StringBuilder();
            int i, j;
            sb.Append("<Worksheet ss:Name='发货单'>");
            sb.Append("<Table ss:ExpandedColumnCount='");
            sb.Append(colCount + 10);
            sb.Append("' ss:ExpandedRowCount='");
            sb.Append(rowCount + 10);
            sb.Append("' x:FullColumns='1'");
            sb.Append(" x:FullRows='1' ss:StyleID='sColumn'");
            sb.Append(" ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'>");

            //定义栏位
            string width = "";
            for (i = 0; i < colCount; i++)
            {

                switch (i)
                {
                    case 2:
                        {
                            width = "300";
                            break;
                        }
                    case 3:
                        {
                            width = "80";
                            break;
                        }
                    case 6:
                        {
                            width = "300";
                            break;
                        }
                    case 11:
                        {
                            width = "80";
                            break;
                        }
                    default:
                        {
                            width = "60";
                            break;
                        }
                }
                sb.Append("<Column ss:StyleID='sColumn' ss:Width='");
                sb.Append(width);
                sb.Append("'/>");
            }
            //定义栏位名称
            sb.Append("<Row>");
            for (i = 0; i < p_Column.GetLength(0); i++)
            {
                sb.Append("<Cell ss:StyleID='sHeader2'><Data ss:Type='String'>");
                sb.Append(p_Column[i, 1]);
                sb.Append("</Data></Cell>");
            }
            sb.Append("</Row>");

            //定义数据内容
            for (j = 0; j < rowCount; j++)
            {
                sb.Append("<Row ss:Height='25'>");
                for (i = 0; i < colCount; i++)
                {
                    if (p_Column[i, 0] != "")
                    {
                        if (dt.Columns[p_Column[i, 0]].DataType.Equals(Type.GetType("System.Int32")))
                        {
                            sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                        else
                        {
                            sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                    }
                    else
                    {
                        sb.Append("<Cell ss:StyleID='sContentWrap'><Data ss:Type='String'></Data></Cell>");
                    }
                }
                sb.Append("</Row>");
            }

            sb.Append("<Row ss:Height='21'>");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);
            sb.Append("' ss:StyleID='sshowdate'><Data ss:Type='String'> </Data></Cell>");
            sb.Append("</Row>");

            //表尾
            sb.Append("</Table>");

            //Sheet结束
            sb.Append("</Worksheet>");
            excelBody = sb.ToString();
        }


        //每日订单明细
        public void DailyOrderExport(DataTable dt, string[,] p_Column, string[] p_ColumnWidth)
        {
            int rowCount = dt.Rows.Count;
            int colCount = p_Column.GetLength(0);
            StringBuilder sb = new StringBuilder();
            int i, j;
            sb.Append("<Worksheet ss:Name='每日订单明细'>");
            sb.Append("<Table ss:ExpandedColumnCount='");
            sb.Append(colCount + 10);
            sb.Append("' ss:ExpandedRowCount='");
            sb.Append(rowCount + 10);
            sb.Append("' x:FullColumns='1'");
            sb.Append(" x:FullRows='1' ss:StyleID='sColumn'");
            sb.Append(" ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'>");

            //定义栏位
            for (i = 0; i < colCount; i++)
            {
                sb.Append("<Column ss:StyleID='sColumn' ss:Width='");
                sb.Append(p_ColumnWidth[i]);
                //sb.Append("px");
                sb.Append("'/>");
            }
            //定义栏位名称
            sb.Append("<Row>");
            for (i = 0; i < p_Column.GetLength(0); i++)
            {
                sb.Append("<Cell ss:StyleID='sHeader2'><Data ss:Type='String'>");
                sb.Append(p_Column[i, 1]);
                sb.Append("</Data></Cell>");
            }
            sb.Append("</Row>");

            //定义数据内容
            for (j = 0; j < rowCount; j++)
            {
                sb.Append("<Row ss:Height='25'>");
                for (i = 0; i < colCount; i++)
                {
                    if (p_Column[i, 0] != "")
                    {
                        if (dt.Columns[p_Column[i, 0]].DataType.Equals(Type.GetType("System.Int32")))
                        {
                            sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                        else
                        {
                            sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                    }
                    else
                    {
                        sb.Append("<Cell ss:StyleID='sContent'><Data ss:Type='String'></Data></Cell>");
                    }
                }
                sb.Append("</Row>");
            }

            sb.Append("<Row ss:Height='21'>");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);
            sb.Append("' ss:StyleID='sshowdate'><Data ss:Type='String'> </Data></Cell>");
            sb.Append("</Row>");

            //表尾
            sb.Append("</Table>");

            //Sheet结束
            sb.Append("</Worksheet>");
            excelBody = sb.ToString();
        }



        //客服每日工作报告
        public void ExporFollowStrage(DataSet ds, string[,] p_Column)
        {
            DataTable dt = ds.Tables[0];
            int rowCount = dt.Rows.Count;
            int colCount = p_Column.GetLength(0);
            StringBuilder sb = new StringBuilder();
            int i, j;
            sb.Append("<Worksheet ss:Name='客服每日工作汇报'>");
            sb.Append("<Table ss:ExpandedColumnCount='");
            sb.Append(colCount + 10);
            sb.Append("' ss:ExpandedRowCount='");
            sb.Append(rowCount + 10);
            sb.Append("' x:FullColumns='1'");
            sb.Append(" x:FullRows='1' ss:StyleID='sColumn'");
            sb.Append(" ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'>");

            //定义栏位
            string width = "";
            for (i = 0; i < colCount; i++)
            {

                switch (i)
                {
                    case 3:
                        {
                            width = "600";
                            break;
                        }

                    default:
                        {
                            width = "80";
                            break;
                        }
                }
                sb.Append("<Column ss:StyleID='sColumn' ss:Width='");
                sb.Append(width);
                sb.Append("'/>");
            }
            //定义栏位名称
            sb.Append("<Row>");
            for (i = 0; i < p_Column.GetLength(0); i++)
            {
                sb.Append("<Cell ss:StyleID='sHeader2'><Data ss:Type='String'>");
                sb.Append(p_Column[i, 1]);
                sb.Append("</Data></Cell>");
            }
            sb.Append("</Row>");

            //定义数据内容
            for (j = 0; j < rowCount; j++)
            {
                sb.Append("<Row ss:Height='25'>");
                for (i = 0; i < colCount; i++)
                {
                    if (p_Column[i, 0] != "")
                    {
                        if (dt.Columns[p_Column[i, 0]].DataType.Equals(Type.GetType("System.Int32")))
                        {
                            sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                        else
                        {
                            sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                    }
                    else
                    {
                        sb.Append("<Cell ss:StyleID='sContentWrap'><Data ss:Type='String'></Data></Cell>");
                    }
                }
                sb.Append("</Row>");
            }



            sb.Append("<Row ss:Height='21' >");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);

            sb.Append("' ss:StyleID='sNotice'><Data ss:Type='String'>");
            sb.Append(" 备注：1、今日客服总呼出量为：");
            sb.Append(ds.Tables[1].Rows[0][0].ToString());
            sb.Append("个");
            sb.Append("</Data></Cell>");
            // sb.Append("<Cell><Data></Data></Cell><Cell><Data></Data></Cell><Cell><Data></Data></Cell>");
            sb.Append("</Row>");




            sb.Append("<Row ss:Height='21'>");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);

            sb.Append("' ss:StyleID='sNotice'><Data ss:Type='String'>");
            sb.Append("       2、处理销售部客户问题：");
            sb.Append(ds.Tables[2].Rows[0][0].ToString());
            sb.Append("个,");
            sb.Append(" 其中没效果客户：");
            sb.Append(ds.Tables[3].Rows[0][0].ToString());
            sb.Append("个,");

            sb.Append("  要求退款客户：");
            sb.Append(ds.Tables[4].Rows[0][0].ToString());
            sb.Append("个,");
            sb.Append("</Data></Cell>");
            sb.Append("</Row>");



            //表尾
            sb.Append("</Table>");

            //Sheet结束
            sb.Append("</Worksheet>");
            excelBody = sb.ToString();
        }

        public void ExportStandard(DataTable dt, string[,] p_Column, string[] p_ColumnWidth)
        {
            int rowCount = dt.Rows.Count;
            int colCount = p_Column.GetLength(0);
            StringBuilder sb = new StringBuilder();
            int i, j;
            sb.Append("<Worksheet ss:Name='Sheet1'>");
            sb.Append("<Table ss:ExpandedColumnCount='");
            sb.Append(colCount + 10);
            sb.Append("' ss:ExpandedRowCount='");
            sb.Append(rowCount + 10);
            sb.Append("' x:FullColumns='1'");
            sb.Append(" x:FullRows='1' ss:StyleID='sColumn'");
            sb.Append(" ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'>");

            //定义栏位
            for (i = 0; i < colCount; i++)
            {
                sb.Append("<Column ss:StyleID='sColumn' ss:Width='");
                sb.Append(p_ColumnWidth[i]);
                //sb.Append("px");
                sb.Append("'/>");
            }
            //定义栏位名称
            sb.Append("<Row>");
            for (i = 0; i < p_Column.GetLength(0); i++)
            {
                sb.Append("<Cell ss:StyleID='sHeader2'><Data ss:Type='String'>");
                sb.Append(p_Column[i, 1]);
                sb.Append("</Data></Cell>");
            }
            sb.Append("</Row>");

            //定义数据内容
            for (j = 0; j < rowCount; j++)
            {
                sb.Append("<Row ss:Height='25'>");
                for (i = 0; i < colCount; i++)
                {
                    if (p_Column[i, 0] != "")
                    {
                        if (dt.Columns[p_Column[i, 0]].DataType.Equals(Type.GetType("System.Int32")))
                        {
                            sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                        else
                        {
                            sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                    }
                    else
                    {
                        sb.Append("<Cell ss:StyleID='sContent'><Data ss:Type='String'></Data></Cell>");
                    }
                }
                sb.Append("</Row>");
            }

            sb.Append("<Row ss:Height='21'>");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);
            sb.Append("' ss:StyleID='sshowdate'><Data ss:Type='String'> </Data></Cell>");
            sb.Append("</Row>");

            //表尾
            sb.Append("</Table>");

            //Sheet结束
            sb.Append("</Worksheet>");
            excelBody = sb.ToString();
        }



        public void DeveridExcel(DataTable dt, string[,] p_Column,string sheet)
        {
            int rowCount = dt.Rows.Count;
            int colCount = p_Column.GetLength(0);
            StringBuilder sb = new StringBuilder();
            int i, j;
            sb.Append("<Worksheet ss:Name='"+sheet+"'>");
            sb.Append("<Table ss:ExpandedColumnCount='");
            sb.Append(colCount + 10);
            sb.Append("' ss:ExpandedRowCount='");
            sb.Append(rowCount + 10);
            sb.Append("' x:FullColumns='1'");
            sb.Append(" x:FullRows='1' ss:StyleID='sColumn'");
            sb.Append(" ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'>");
            #region 定义栏位
            //定义栏位
            string width = "150";
            for (i = 0; i < colCount; i++)
            {

                
                sb.Append("<Column ss:StyleID='sColumn' ss:Width='");
                sb.Append(width);
                sb.Append("'/>");
            }
            #endregion
            //定义栏位名称
            sb.Append("<Row>");
            for (i = 0; i < p_Column.GetLength(0); i++)
            {
                sb.Append("<Cell ss:StyleID='sHeader2'><Data ss:Type='String'>");
                sb.Append(p_Column[i, 1]);
                sb.Append("</Data></Cell>");
            }
            sb.Append("</Row>");

            //定义数据内容
            for (j = 0; j < rowCount; j++)
            {
                sb.Append("<Row ss:Height='25'>");
                for (i = 0; i < colCount; i++)
                {
                    if (p_Column[i, 0] != "")
                    {
                        if (dt.Columns[p_Column[i, 0]].DataType.Equals(Type.GetType("System.Int32")))
                        {
                            sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                        else
                        {
                            sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                            sb.Append(dt.Rows[j][p_Column[i, 0]]);
                            sb.Append("</Data></Cell>");
                        }
                    }
                    else
                    {
                        sb.Append("<Cell ss:StyleID='sContentWrap'><Data ss:Type='String'></Data></Cell>");
                    }
                }
                sb.Append("</Row>");
            }

            sb.Append("<Row ss:Height='21'>");
            sb.Append("<Cell ss:MergeAcross='");
            sb.Append(colCount - 1);
            sb.Append("' ss:StyleID='sshowdate'><Data ss:Type='String'> </Data></Cell>");
            sb.Append("</Row>");

            //表尾
            sb.Append("</Table>");

            //Sheet结束
            sb.Append("</Worksheet>");
            excelBody += sb.ToString();
        }



    }

}

